1.版本

select version();
+-----------------------------------------------------------------------------------------------------------------+
|                                                     version                                                     |
+-----------------------------------------------------------------------------------------------------------------+
| PostgreSQL 11.5 on x86_64-apple-darwin18.6.0, compiled by Apple LLVM version 10.0.1 (clang-1001.0.46.4), 64-bit |
+-----------------------------------------------------------------------------------------------------------------+
(1 row)

or on shell
pg_config | grep VERSION
VERSION = PostgreSQL 11.5

2.Server 啟動時間與換算成已開機時間

select pg_postmaster_start_time();
+-------------------------------+
|   pg_postmaster_start_time    |
+-------------------------------+
| 2019-09-17 12:35:59.093472+08 |
+-------------------------------+

可以看到是帶時區精準度較高的格式,我們可以利用函數date_trunc,取到秒.
以利判讀.

select date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;
+--------------------------+
|          uptime          |
+--------------------------+
| @ 1 hour 54 mins 32 secs |
+--------------------------+

3.列出Server上的Database

select datname as db_name
  from pg_database
 where datname 
   not in ('postgres', 'template0', 'template1')
    -- 屏蔽系統資料庫,與系統模板資料庫
 order by datname;
+---------+
| db_name |
+---------+
| advwork |
| miku    |
+---------+
(2 rows)

同場加映查看資料庫大小

SELECT pg_size_pretty(
       pg_database_size(
       current_database()));

+----------------+
| pg_size_pretty |
+----------------+
| 582 MB         |
+----------------+
(1 row)

所以我們可以一起查看

select datname as db_name
     , pg_size_pretty(
       pg_database_size(
       datname)) as db_size
  from pg_database
 where datname 
   not in ('postgres', 'template0', 'template1')
 order by datname;

+---------+---------+
| db_name | db_size |
+---------+---------+
| advwork | 113 MB  |
| miku    | 582 MB  |
+---------+---------+
(2 rows)

4.列出設定檔位置及目前設定情況不是default的

show config_file;

+-----------------------------------------+
|               config_file               |
+-----------------------------------------+
| /usr/local/var/postgres/postgresql.conf |
+-----------------------------------------+
(1 row)

select name
     , source
     , setting
  from pg_settings
 where source != 'default'
   and source != 'override'
 order by 2, 1;
+----------------------------+----------------------+--------------------+
|            name            |        source        |      setting       |
+----------------------------+----------------------+--------------------+
| application_name           | client               | psql               |
| client_encoding            | client               | UTF8               |
| DateStyle                  | configuration file   | ISO, MDY           |
| TimeZone                   | configuration file   | Asia/Taipei        |
| default_text_search_config | configuration file   | pg_catalog.english |
| dynamic_shared_memory_type | configuration file   | posix              |
| lc_messages                | configuration file   | C                  |
| lc_monetary                | configuration file   | C                  |
| lc_numeric                 | configuration file   | C                  |
| lc_time                    | configuration file   | C                  |
| log_timezone               | configuration file   | Asia/Taipei        |
| max_connections            | configuration file   | 100                |
| max_wal_size               | configuration file   | 1024               |
| min_wal_size               | configuration file   | 80                 |
| shared_buffers             | configuration file   | 16384              |
| max_stack_depth            | environment variable | 2048               |
+----------------------------+----------------------+--------------------+
(16 rows)